﻿CREATE PROCEDURE [dbo].[proc_Project_Getlist_G20141226]
	(
		@CompanyId int,
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@Audit int,
		@Stext nvarchar(50),
		@StartIndex int,
		@EndIndex int,
		@Je varchar(50),
		@Product1 int, --20141229 新增按产品查询
		@Product2 int,
		@Product3 int,
		@TypeId int
	)
AS
Begin
	
	declare @Sql nvarchar(4000),@SqlStr Nvarchar(4000)
	set @Sql=''
	if @CompanyId<>0
	begin
		set @Sql=@Sql+'  Companyid ='+Convert(varchar,@CompanyId)+' and '
	end
    if @Audit<>0
    begin
		set @Sql=@Sql+' FinanceAudit='+convert(varchar,@Audit)+' and '
    end
    
	if @TypeId <>0
	   set @Sql=@Sql+' TypeId='+CONVERT(varchar,@TypeId)+' and '

    if @Product3<>-1
    begin
		set @Sql=@Sql+' Productid='+convert(varchar,@Product3)+' and '
    end 
    else 
    begin
		if @Product1<>-1
		begin
			set @Sql=@Sql+' Productid in (select  id from [Product] where CateOneId='+convert(varchar,@Product1)+') and '
		end
		if @Product2<>-1
		begin
			set @Sql=@Sql+' Productid in (select  id from [Product] where  [CateTwoId]='+convert(varchar,@Product2)+') and '
		end 
    end
    if @Stext<>''
    begin
		set @Sql =@Sql+' CusName like ''%'+@Stext+'%'' and  '
    end
    if @Je <>''
    begin
		set @Sql =@Sql+' CostPrice>'+@Je+' and '
    end

	set @SqlStr='

	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,'''+@StartDate+''+' 00:00:00'')
	Set @eRq = Convert(Datetime,'''+@EndDate+''+' 23:59:59'')

	;WITH List as (Select ROW_NUMBER() OVER (ORDER BY IsFinish DESC,FinanceAudit, FinishDate ,CreateDate ,id)AS Row
		,Id
		,ProjectId
		,OrderId
		,CusName
		,ProductName
		,SupplierName
		,CostPrice
		,TypeName
		,IsFinish
		,ISNULL(FinishDate,''1900-01-01'') As FinishDate
		,FinanceAudit
		,ISNULL(FinanceAuditDate,''1900-01-01'') As FinanceAuditDate
		,Salesman
		,CreateDate
		,NOE_Flag
		From Project
		Where '+@Sql+'  CreateDate>=@sRq And CreateDate<=@eRq				
	)
	
	Select *,(Select Count(1) From List) AS DataCount
		,(select sum(CostPrice) from list) AS CostPrice_SUM
	From list Where Row between '+Convert(varchar,@StartIndex)+' and '+Convert(varchar,@EndIndex)  +'
	Order By Row
	'

exec (@SqlStr)
--print @Sql	
End
